DECLARE @startDateString DATETIME
DECLARE @endDateString DATETIME
SET @startDateString = '2009-01-1 00:00:00.000'
SET @endDateString = '2009-12-31 23:59:59.999'

SELECT Diagnosis.Name , COUNT(diagnosistopatient.diagnosis_id) AS [Count] FROM diagnosis, diagnosistopatient, stay WHERE diagnosis.id = diagnosistopatient.diagnosis_id AND diagnosistopatient.patient_id = stay.patient_id AND stay.checkinDate > @startDateString AND stay.checkoutDate < '2009-12-31 23:59:59.999' GROUP BY diagnosis.name